//SET DIRECTORY FIRST
*cd " "

//1) CLIMATE-SIGNIFICANT EXPENDITURES
// https://stats.oecd.org/Index.aspx?datasetcode=SGCFD --> Export / Text file (CSV)
import delimited Vincent&Mango_RawData_File1of7.csv, clear 

rename ïcountry ccode
drop if ccode == "ALL_WAVG" | ccode == "AUS" | ccode == "EU_UNW" | ccode == "EU_W" | ccode == "ISR" | ccode == "JPN" | ccode == "OAVG_W" | ccode == "OECD_UNW" | ccode == "UNW_AVG" // Only European countries
keep if sector == "S13" | sector == "S1312_1313" // S13 = "General Government" , S1312_1313 = "Subnational Government"
keep if indicator == "EXPENDITURE" // drop Investments
keep if measure == "PER_CAP_REAL" | measure == "GDP_SH" | measure == "PER_CAP" // 2015 USD PPP 
keep ccode sector measure year value

reshape wide value , i(ccode year sector) j(measure) string
reshape wide valueGDP_SH valuePER_CAP_REAL valuePER_CAP , i(ccode year) j(sector) string

rename valueGDP_SHS13 gg_gdp // new variable: GENERAL government climate-significant expenditures as %GDP
rename valuePER_CAP_REALS13 gg_pc_usr // new variable: GENERAL government climate-significant per capita expenditures (2015 USD real) --> $
rename valuePER_CAPS13 gg_pc_usppp // new variable: GENERAL government climate-significant per capita expenditures (USD PPP)
rename valueGDP_SHS1312_1313 sng_gdp // new variable: SUBNATIONAL government climate-significant expenditures as %GDP
rename valuePER_CAP_REALS1312_1313 sng_pc_usr // new variable: SUBNATIONAL government climate-significant per capita expenditures (2015 USD real) --> $
rename valuePER_CAPS1312_1313 sng_pc_usppp // new variable: SUBNATIONAL government climate-significant per capita expenditures (USD PPP)
gen cg_gdp = gg_gdp - sng_gdp // new (imperfect) variable: CENTRAL government climate-significant expenditures as %GDP
gen cg_pc_usr = gg_pc_usr - sng_pc_usr // new (imperfect) variable: CENTRAL government climate-significant expenditures relative to GDP (2015 USD real) --> $
gen cg_pc_usppp = gg_pc_usppp - sng_pc_usppp // new (imperfect) variable: CENTRAL government climate-significant expenditures relative to GDP (USD PPP)

replace gg_pc_usr = gg_pc_usr / 100 // reinterpretation: 1 unit is $ 100 
replace sng_pc_usr = sng_pc_usr / 100 // reinterpretation: 1 unit is $ 100 
replace cg_pc_usr = cg_pc_usr / 100 // reinterpretation: 1 unit is $ 100 

replace gg_pc_usppp = gg_pc_usppp / 100 // reinterpretation: 1 unit is $ 100 
replace sng_pc_usppp = sng_pc_usppp / 100 // reinterpretation: 1 unit is $ 100 
replace cg_pc_usppp = cg_pc_usppp / 100 // reinterpretation: 1 unit is $ 100 

format *gdp *pc* %3.2f

fillin ccode year 
drop _fillin

label variable ccode "Country ISO 3-dig"
label variable year "2001-2019"
label variable gg_gdp "General Government Climate-Significant Expenditures (%GDP)"
label variable gg_pc_usr "General Government Climate-Significant Expenditures ($ pc)" 
label variable gg_pc_usppp "General Government Climate-Significant Expenditures (PPP)" 
label variable sng_gdp "Subnational Government Climate-Significant Expenditures (%GDP)"
label variable sng_pc_usr "Subnational Government Climate-Significant Expenditures ($ pc)"
label variable sng_pc_usppp "Subnational Government Climate-Significant Expenditures (PPP)"  
label variable cg_gdp "Central Government Climate-Significant Expenditures (%GDP)"
label variable cg_pc_usr "Central Government Climate-Significant Expenditures ($ pc)"
label variable cg_pc_usppp "Central Government Climate-Significant Expenditures (PPP)"

label value gg_gdp "Pct"
label value gg_pc_usr "One_Hundred_USD_Real"
label value gg_pc_usppp "One_Hundred_USD_PPP"
label value sng_gdp "Pct"
label value sng_pc_usr "One_Hundred_USD_Real"
label value sng_pc_usppp "One_Hundred_USD_PPP"
label value cg_gdp "Pct"
label value cg_pc_usr "One_Hundred_USD_Real"
label value cg_pc_usppp "One_Hundred_USD_PPP"

save "Vincent&Mango_ProcessedData_File1of6.dta" , replace

//2) RESOURCE PRODUCTIVITY
//https://ec.europa.eu/eurostat/databrowser/view/t2020_rl100/default/table?lang=en --> Download / Full Dataset [T2020_RL100] / SDMX-CSV
import delimited Vincent&Mango_RawData_File2of7.csv, clear 
drop dataflow lastupdate freq obs_flag

*Keep only Europe-30
gen ccode = "AUT" if geo == "AT"
replace ccode = "BEL" if geo == "BE"
replace ccode = "BGR" if geo == "BG"
replace ccode = "HRV" if geo == "HR"
replace ccode = "CZE" if geo == "CZ"
replace ccode = "DNK" if geo == "DK"
replace ccode = "EST" if geo == "EE"
replace ccode = "FIN" if geo == "FI"
replace ccode = "FRA" if geo == "FR"
replace ccode = "DEU" if geo == "DE"
replace ccode = "GRC" if geo == "EL"
replace ccode = "HUN" if geo == "HU"
replace ccode = "ISL" if geo == "IS"
replace ccode = "IRL" if geo == "IE"
replace ccode = "ITA" if geo == "IT"
replace ccode = "LVA" if geo == "LV"
replace ccode = "LTU" if geo == "LT"
replace ccode = "LUX" if geo == "LU"
replace ccode = "NLD" if geo == "NL"
replace ccode = "NOR" if geo == "NO"
replace ccode = "POL" if geo == "PL"
replace ccode = "PRT" if geo == "PT"
replace ccode = "ROU" if geo == "RO"
replace ccode = "SVK" if geo == "SK"
replace ccode = "SVN" if geo == "SI"
replace ccode = "ESP" if geo == "ES"
replace ccode = "SWE" if geo == "SE"
replace ccode = "CHE" if geo == "CH"
replace ccode = "TUR" if geo == "TR"
replace ccode = "GBR" if geo == "UK"
drop if ccode == ""
drop geo

reshape wide obs_value , i(ccode time) j(unit) string

rename time year
rename obs_valueEUR_KG_CLV15 rp // real GDP (2015 Euros, chained) --> Default
rename obs_valuePPS_KG rp_ppp // GDP PPP
rename obs_valueI00 rp_index // RP Index 100 = 2000

order ccode year rp rp_ppp rp_index 
format rp* %3.2f

label variable rp "Resource Productivity (€/Kg)"
label variable rp_ppp "Resource Productivity (PPS/Kg)"
label variable rp_index "Resource Productivity (100=2000)"

label value rp "EUR_Real"
label value rp_ppp "PPS"
label value rp_index "Index"

save "Vincent&Mango_ProcessedData_File2of6.dta" , replace


//3) GDP PER CAPITA
//https://stats.oecd.org/index.aspx?queryid=66948 --> Frequency: Annual / Export / Text file (CSV)
import delimited  Vincent&Mango_RawData_File3of7.csv , clear
keep if measure == "HVPVOBARSA" // 2015 USD PPP
keep if frequency == "A" // Annual
keep ïlocation time value
rename ïlocation ccode
rename time year
rename value gdp_pc_usr // GDP per capita 2015 USD PPP
format gdp_pc_usr %25.1fc

replace gdp_pc_usr = gdp_pc_usr / 100 // reinterpretation: 1 unit is $ 100

label variable gdp_pc_usr "Real GDP per capita (2015 USD PPP)"
label value gdp_pc_usr "One_Hundred_USD_Real"

save "Vincent&Mango_ProcessedData_File3of6.dta" , replace


//4) RECYCLING RATE
// https://ec.europa.eu/eurostat/databrowser/view/cei_wm011/default/table?lang=en --> Download / Full Dataset [CEI_WM011] / SDMX-CSV
import delimited Vincent&Mango_RawData_File4of7.csv , clear // 
drop dataflow lastupdate freq wst_oper unit obs_flag

*Keep only Europe-30
gen ccode = "AUT" if geo == "AT"
replace ccode = "BEL" if geo == "BE"
replace ccode = "BGR" if geo == "BG"
replace ccode = "HRV" if geo == "HR"
replace ccode = "CZE" if geo == "CZ"
replace ccode = "DNK" if geo == "DK"
replace ccode = "EST" if geo == "EE"
replace ccode = "FIN" if geo == "FI"
replace ccode = "FRA" if geo == "FR"
replace ccode = "DEU" if geo == "DE"
replace ccode = "GRC" if geo == "EL"
replace ccode = "HUN" if geo == "HU"
replace ccode = "ISL" if geo == "IS"
replace ccode = "IRL" if geo == "IE"
replace ccode = "ITA" if geo == "IT"
replace ccode = "LVA" if geo == "LV"
replace ccode = "LTU" if geo == "LT"
replace ccode = "LUX" if geo == "LU"
replace ccode = "NLD" if geo == "NL"
replace ccode = "NOR" if geo == "NO"
replace ccode = "POL" if geo == "PL"
replace ccode = "PRT" if geo == "PT"
replace ccode = "ROU" if geo == "RO"
replace ccode = "SVK" if geo == "SK"
replace ccode = "SVN" if geo == "SI"
replace ccode = "ESP" if geo == "ES"
replace ccode = "SWE" if geo == "SE"
replace ccode = "CHE" if geo == "CH"
replace ccode = "TUR" if geo == "TR"
replace ccode = "GBR" if geo == "UK"
drop if ccode == ""
drop geo

rename obs_value rr
rename time year
order ccode year rr
format rr %3.2f

label variable rr "Recycling Rate of Municipal Waste"
label value rr "Pct"

save "Vincent&Mango_ProcessedData_File4of6.dta" , replace


//5) ENVIRONMENTAL TAXES
// https://ec.europa.eu/eurostat/databrowser/view/env_ac_tax/default/table?lang=en --> Download / Full Dataset [ENV_AC_TAX] / SDMX-CSV
import delimited Vincent&Mango_RawData_File5of7, clear 
drop dataflow lastupdate freq obs_flag

keep if tax == "ENV" // Total environmental taxes
keep if unit == "PC_GDP" // Revenues as %GDP

*Keep only Europe-30
gen ccode = "AUT" if geo == "AT"
replace ccode = "BEL" if geo == "BE"
replace ccode = "BGR" if geo == "BG"
replace ccode = "HRV" if geo == "HR"
replace ccode = "CZE" if geo == "CZ"
replace ccode = "DNK" if geo == "DK"
replace ccode = "EST" if geo == "EE"
replace ccode = "FIN" if geo == "FI"
replace ccode = "FRA" if geo == "FR"
replace ccode = "DEU" if geo == "DE"
replace ccode = "GRC" if geo == "EL"
replace ccode = "HUN" if geo == "HU"
replace ccode = "ISL" if geo == "IS"
replace ccode = "IRL" if geo == "IE"
replace ccode = "ITA" if geo == "IT"
replace ccode = "LVA" if geo == "LV"
replace ccode = "LTU" if geo == "LT"
replace ccode = "LUX" if geo == "LU"
replace ccode = "NLD" if geo == "NL"
replace ccode = "NOR" if geo == "NO"
replace ccode = "POL" if geo == "PL"
replace ccode = "PRT" if geo == "PT"
replace ccode = "ROU" if geo == "RO"
replace ccode = "SVK" if geo == "SK"
replace ccode = "SVN" if geo == "SI"
replace ccode = "ESP" if geo == "ES"
replace ccode = "SWE" if geo == "SE"
replace ccode = "CHE" if geo == "CH"
replace ccode = "TUR" if geo == "TR"
replace ccode = "GBR" if geo == "UK"
drop if ccode == ""

drop geo tax unit
rename obs_value etax_gdp
rename time year
order ccode year etax_gdp
format etax_gdp %3.2f

label variable etax_gdp "Environmental Tax Revenues as %GDP"
label value etax_gdp "Pct"

save "Vincent&Mango_ProcessedData_File5of6.dta" , replace


//6) OTHER CONTROL VARIABLES
//Source: WORLD DEVELOPMENT INDICATORS --> 	https://databank.worldbank.org/source/world-development-indicators
//Series:
*NY.GDP.PCAP.KD // GDP per capita, PPP (constant 2015 US$)
*NY.GDP.PCAP.PP.KD // GDP per capita (constant 2017 international $)
*EG.FEC.RNEW.ZS // Renewable energy consumption (% of total final energy consumption)
*EN.POP.DNST // Population density (people per sq. km of land area)
*NV.SRV.TOTL.ZS // Services, value added (% of GDP)
*GB.XPD.RSDV.GD.ZS // Research and development expenditure (% of GDP) 
import excel Vincent&Mango_RawData_File6of7.xlsx , sheet("Data") firstrow
drop ïcountryname seriesname
destring yr* , replace force
drop if countrycode == ""

reshape long yr, i(countrycode seriescode) j(year)
replace seriescode = "GDPPCAP" if seriescode == "NY.GDP.PCAP.KD" 
replace seriescode = "GDPPCAPPP" if seriescode == "NY.GDP.PCAP.PP.KD"
replace seriescode = "RNEW" if seriescode == "EG.FEC.RNEW.ZS" 
replace seriescode = "POPDNST" if seriescode == "EN.POP.DNST"
replace seriescode = "SRV" if seriescode == "NV.SRV.TOTL.ZS"
replace seriescode = "RSDV" if seriescode == "GB.XPD.RSDV.GD.ZS"

reshape wide yr , i(countrycode year) j(seriescode) string

rename countrycode ccode
rename yrGDPPCAP gdp_pc
rename yrGDPPCAPPP gdp_pc_usr17
rename yrPOPDNST pop_den
rename yrRNEW ren_tot
rename yrRSDV randd_gdp
rename yrSRV serv_gdp

replace gdp_pc = gdp_pc / 100 // reinterpretation: 1 unit is $ 100 
replace gdp_pc_usr17 = gdp_pc_usr17 / 100 // reinterpretation: 1 unit is $ 100 
replace pop_den = pop_den / 10 // reinterpretation: 1 unit are 10 persons

label variable gdp_pc "GDP per capita 2015 USD"
label variable gdp_pc_usr17 "GDP per capita 2017 USD PPP"
label variable ren_tot "Share of Renewable Energies in Final Consumption"
label variable serv_gdp "VA in Services as %GDP"
label variable pop_den "Population Density"
label variable randd_gdp "R&D as %GDP"

label value gdp_pc "One_Hundred_USD_Constant"
label value gdp_pc_usr17 "One_Hundred_USD_Real"
label value ren_tot "Pct"
label value serv_gdp "Pct"
label value pop_den "Ten_Persons_Km2"
label value randd_gdp "Pct"

format ren_tot *_gdp %3.2f
format gdp* pop_den %25.1fc
order ccode year gdp_pc gdp_pc_usr17 ren_tot serv_gdp pop_den randd_gdp

save "Vincent&Mango_ProcessedData_File6of6.dta" , replace


//7) DATASET GENERATION

use "Vincent&Mango_ProcessedData_File1of6.dta" , clear

merge m:1 ccode year using "Vincent&Mango_ProcessedData_File2of6.dta"
drop if _merge == 2
drop _merge
merge m:1 ccode year using "Vincent&Mango_ProcessedData_File3of6.dta"
drop if _merge == 2
drop _merge
merge m:1 ccode year using "Vincent&Mango_ProcessedData_File4of6.dta"
drop if _merge == 2
drop _merge
merge m:1 ccode year using "Vincent&Mango_ProcessedData_File5of6.dta"
drop if _merge == 2
drop _merge
merge m:1 ccode year using "Vincent&Mango_ProcessedData_File6of6.dta"
drop if _merge == 2
drop _merge

replace rr = . if ccode == "ISL" & rr == 0 // Error in raw data: they are missing values
order ccode year rp sng_pc_usr cg_pc_usr gdp_pc_usr rr ren_tot serv_gdp pop_den etax_gdp randd_gdp sng_gdp cg_gdp sng_pc_usppp cg_pc_usppp gg_pc_usr gg_gdp gg_pc_usppp
save "Vincent&Mango_DataFile1.dta" , replace

describe
/*

              storage   display    value
variable name   type    format     label      variable label
---------------------------------------------------------------------------------------------------------------------------------
ccode           str9    %9s                   Country ISO 3-dig
year            int     %8.0g                 2001-2019
rp              float   %3.2f      EUR_Real   Resource Productivity (€/Kg)
sng_pc_usr      float   %3.2f      One_Hundred_USD_Real
                                              Subnational Government Climate-Significant Expenditures ($ pc)
cg_pc_usr       float   %3.2f      One_Hundred_USD_Real
                                              Central Government Climate-Significant Expenditures ($ pc)
gdp_pc_usr      float   %25.1fc    One_Hundred_USD_Real
                                              Real GDP per capita (2015 USD PPP)
rr              float   %3.2f      Pct        Recycling Rate of Municipal Waste
ren_tot         double  %3.2f      Pct        Share of Renewable Energies in Final Consumption
serv_gdp        double  %3.2f      Pct        VA in Services as %GDP
pop_den         double  %25.1fc    Ten_Persons_Km2
                                              Population Density
etax_gdp        float   %3.2f      Pct        Environmental Tax Revenues as %GDP
randd_gdp       double  %3.2f      Pct        R&D as %GDP
sng_gdp         float   %3.2f      Pct        Subnational Government Climate-Significant Expenditures (%GDP)
cg_gdp          float   %3.2f      Pct        Central Government Climate-Significant Expenditures (%GDP)
sng_pc_usppp    float   %3.2f      One_Hundred_USD_PPP
                                              Subnational Government Climate-Significant Expenditures (PPP)
cg_pc_usppp     float   %3.2f      One_Hundred_USD_PPP
                                              Central Government Climate-Significant Expenditures (PPP)
gg_pc_usr       float   %3.2f      One_Hundred_USD_Real
                                              General Government Climate-Significant Expenditures ($ pc)
gg_gdp          float   %3.2f      Pct        General Government Climate-Significant Expenditures (%GDP)
gg_pc_usppp     float   %3.2f      One_Hundred_USD_PPP
                                              General Government Climate-Significant Expenditures (PPP)
rp_ppp          float   %3.2f      PPS        Resource Productivity (PPS/Kg)
rp_index        float   %3.2f      Index      Resource Productivity (100=2000)
gdp_pc          double  %25.1fc    One_Hundred_USD_Constant
                                              GDP per capita 2015 USD
gdp_pc_usr17    double  %25.1fc    One_Hundred_USD_Real
                                              GDP per capita 2017 USD PPP
co2             double  %25.2fc    Kilotons   CO2 Emissions
co2_pc          double  %25.2fc    Metric_tons
                                              CO2 per capita
co2_gdp         double  %25.2fc    USD_constant
                                              CO2 Emissions per GDP (2015 USD)										  
------------------------------------------------------------------------------------------------------------------
*/


